﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DuJianliang.ApplicationPlatform.Common;

namespace DuJianliang.ApplicationPlatform.Flow
{
    public partial class FlowManager : System.Web.UI.Page
    {
        private const int OPERATION_CREATE = 1;   // 发起
        private const int OPERATION_AGREE = 2;    // 同意
        private const int OPERATION_REJECT = 3;   // 驳回        
        private const int OPERATION_INVALID = 4;  // 作废

        #region 属性

        public string UrlParam_FlowID
        {
            get
            {
                object obj = ViewState["FlowID"];
                return obj != null ? obj.ToString() : string.Empty;                
            }
            set
            {
                ViewState["FlowID"] = value;
            }
        }

        public string UrlParam_FlowInstanceID
        {
            get
            {
                object obj = ViewState["FlowInstanceID"];
                return obj != null ? obj.ToString() : string.Empty;
            }
            set
            {
                ViewState["FlowInstanceID"] = value;
            }
        }

        private string LastStepID
        {
            get
            {
                object obj = ViewState["LastStepID"];
                return obj != null ? obj.ToString() : string.Empty;
            }
            set
            {
                ViewState["LastStepID"] = value;
            }
        }

        public string CurrentStepID
        {
            get
            {
                object obj = ViewState["CurrentStepID"];
                return obj != null ? obj.ToString() : string.Empty;
            }
            set
            {
                ViewState["CurrentStepID"] = value;
            }
        }

        private int LastStepNumber
        {
            get
            {
                object obj = ViewState["LastStepNumber"];
                return obj != null ? int.Parse(obj.ToString()) : 0;
            }
            set
            {
                ViewState["LastStepNumber"] = value;
            }
        }

        private int CurrentStepNumber
        {
            get
            {
                return this.LastStepNumber + 1;
            }           
        }

        private int NextStepNumber
        {
            get
            {
                return this.CurrentStepNumber + 1;
            }
        }

        private string TableName
        {
            get
            {
                object obj = ViewState["TableName"];
                return obj != null ? obj.ToString() : string.Empty;
            }
            set
            {
                ViewState["TableName"] = value;
            }
        }

        private string FormPath
        {
            get
            {
                object obj = ViewState["FormPath"];
                return obj != null ? obj.ToString() : string.Empty;
            }
            set
            {
                ViewState["FormPath"] = value;
            }
        }
        
        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            // 接收参数
            this.UrlParam_FlowID = Request.QueryString["FlowID"];
            this.UrlParam_FlowInstanceID = Request.QueryString["FlowInstanceID"];

            // 如果FlowInstanceID不存在，则创建新的
            if (string.IsNullOrWhiteSpace(this.UrlParam_FlowInstanceID))
            {
                this.UrlParam_FlowInstanceID = Guid.NewGuid().ToString();
            }            

            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();             

            try
            {
                // 如果流程ID不存在，则尝试使用FlowInstanceID到FlowInstance表中获取，如获取不到则表示没有流程数据。
                if (string.IsNullOrWhiteSpace(this.UrlParam_FlowID))
                {
                    DataTable dt = this.GetFlowInstanceInfo(database, new Guid(this.UrlParam_FlowInstanceID));
                    if (dt.Rows.Count > 0)
                    {
                        this.UrlParam_FlowID = dt.Rows[0]["FlowID"].ToString();
                    }
                    else
                    {
                        return;
                    }
                }

                string currentUser = helper.CheckAndGetCurrentOperator();
                this.LastStepNumber = this.GetLastStepNumber(database, new Guid(this.UrlParam_FlowInstanceID));
                this.FormPath = this.GetFormPath(database, this.UrlParam_FlowID);
                this.TableName = this.GetTableName(database, this.UrlParam_FlowID);
                this.LastStepID = this.GetLastStepID(database, new Guid(this.UrlParam_FlowInstanceID));
                this.CurrentStepID = this.GetCurrentStepID(database, new Guid(this.UrlParam_FlowInstanceID), currentUser);

                if (string.IsNullOrWhiteSpace(this.CurrentStepID) && !FlowHelper.HasFlowData(new Guid(this.UrlParam_FlowInstanceID)))
                {
                    this.CurrentStepID = this.GetFirstStep(database, this.UrlParam_FlowID);
                }

                this.txtFlowName.Text = this.GetFlowName(database, this.UrlParam_FlowID);
                this.txtStepName.Text = this.GetCurrentStepName(database, this.CurrentStepID);
                this.hidFlowInstanceID.Value = this.UrlParam_FlowInstanceID;
                this.hidFormPath.Value = this.FormPath;
                
                this.InitButton(new Guid(this.UrlParam_FlowInstanceID));

                if (btnCreate.Visible)
                {
                    txtSubject.Enabled = true;
                } 
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                Helper.Instance.SetException(ex);
            }

            if (!Page.IsPostBack)
            {
                try
                {
                    // 显示流程主题                    
                    DataTable dataTable = this.GetFlowInstanceInfo(database, new Guid(this.UrlParam_FlowInstanceID));
                    if (dataTable.Rows.Count > 0)
                    {
                        object objSubject = dataTable.Rows[0]["Subject"];
                        if (objSubject != null && objSubject != DBNull.Value)
                        {
                            this.txtSubject.Text = objSubject.ToString();
                        }
                    }
                    else
                    { 
                        this.txtSubject.Text = string.Format("{0}-{1}-{2}", CurrentInfo.Instance.CurrentUser.UserName
                            , DateTime.Now.ToString("yyyy-MM-dd-HHmmss"), this.txtFlowName.Text);
                    }
                }                
                catch (Exception ex)
                {
                    Helper.Instance.SetException(ex);
                }
            }
        }

        protected void btnCreate_Click(object sender, EventArgs e)
        {
            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();
            DbConnection dbConnection = null;
            DbTransaction dbTransaction = null;
            DbCommand dbCommand = null;
            List<DbCommand> dbCommandList = new List<DbCommand>();

            List<string> operatorEmails = new List<string>();

            try
            {
                Guid flowInstanceID = new Guid(this.UrlParam_FlowInstanceID);
                string subject = this.txtSubject.Text.Trim();
                string opinion = this.txtOpinion.Text.Trim();
                string currentUser = helper.CheckAndGetCurrentOperator();
                DateTime operationDate = DateTime.Now;

                string firstStepID = this.GetFirstStep(database, this.UrlParam_FlowID);
                DataTable dataTable = this.GetChildSteps(database, firstStepID);
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    dbCommand = this.GetDbCommand_FlowInstanceStep_Insert2(database, flowInstanceID
                        , dataRow["StepID"].ToString(), this.NextStepNumber, false);
                    dbCommandList.Add(dbCommand);

                    try
                    {
                        string email = FlowHelper.GetOperatorEmail(this.UrlParam_FlowID, dataRow["StepID"].ToString());
                        operatorEmails.Add(email);
                    }
                    catch(Exception ex1)
                    {
                        Logger.Write(null, 1, "email sender", "exception", ex1);
                    }
                }

                dbCommand = this.GetDbCommand_FlowInstance_Insert(database, flowInstanceID, this.UrlParam_FlowID
                    , subject, currentUser, operationDate, this.CurrentStepID, this.CurrentStepNumber
                    , OPERATION_CREATE, operationDate, opinion, currentUser, false);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstanceStep_Insert(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_CREATE, operationDate, opinion, currentUser, true);
                dbCommandList.Add(dbCommand);

                dbConnection = database.CreateConnection();
                dbConnection.Open();
                dbTransaction = dbConnection.BeginTransaction();

                foreach (DbCommand dbCmd in dbCommandList)
                {
                    database.ExecuteNonQuery(dbCmd, dbTransaction);
                }

                dbTransaction.Commit();

                EmailSender.Send(operatorEmails);                

                this.ShowStepInfoPage(this.UrlParam_FlowInstanceID);            
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }
                helper.SetException(ex);
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
            }
        }

        protected void btnAgree_Click(object sender, EventArgs e)
        {
            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();
            DbConnection dbConnection = null;
            DbTransaction dbTransaction = null;
            DbCommand dbCommand = null;
            List<DbCommand> dbCommandList = new List<DbCommand>();

            List<string> operatorEmails = new List<string>();

            try
            {                
                Guid flowInstanceID = new Guid(this.UrlParam_FlowInstanceID);
                string opinion = this.txtOpinion.Text.Trim();
                string currentUser = helper.CheckAndGetCurrentOperator();
                DateTime operationDate = DateTime.Now;

                bool flowComplated = true;
                DataTable dataTable = this.GetChildSteps(database, this.CurrentStepID);
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    flowComplated = false;
                    dbCommand = this.GetDbCommand_FlowInstanceStep_Insert2(database, flowInstanceID
                        , dataRow["StepID"].ToString(), this.NextStepNumber, false);
                    dbCommandList.Add(dbCommand);

                    try
                    {
                        string email = FlowHelper.GetOperatorEmail(this.UrlParam_FlowID, dataRow["StepID"].ToString());
                        operatorEmails.Add(email);
                    }
                    catch (Exception ex1)
                    {
                        Logger.Write(null, 1, "email sender", "exception", ex1);
                    }
                }

                dbCommand = this.GetDbCommand_FlowInstance_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_AGREE, operationDate, opinion, currentUser, flowComplated);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstanceStep_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_AGREE, operationDate, opinion, currentUser, true);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstanceStep_Update2(database, flowInstanceID, this.CurrentStepNumber, true);
                dbCommandList.Add(dbCommand);

                dbConnection = database.CreateConnection();
                dbConnection.Open();
                dbTransaction = dbConnection.BeginTransaction();

                foreach (DbCommand dbCmd in dbCommandList)
                {
                    database.ExecuteNonQuery(dbCmd, dbTransaction);
                }

                dbTransaction.Commit();

                EmailSender.Send(operatorEmails);

                this.ShowStepInfoPage(this.UrlParam_FlowInstanceID);       
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }
                helper.SetException(ex);
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
            }
        }

        protected void btnReject_Click(object sender, EventArgs e)
        {
            string rejectStepID = hidRejectStepID.Value;

            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();
            DbConnection dbConnection = null;
            DbTransaction dbTransaction = null;
            DbCommand dbCommand = null;
            List<DbCommand> dbCommandList = new List<DbCommand>();

            List<string> operatorEmails = new List<string>();

            try
            {                
                Guid flowInstanceID = new Guid(this.UrlParam_FlowInstanceID);                
                string opinion = this.txtOpinion.Text.Trim();
                string currentUser = helper.CheckAndGetCurrentOperator();
                DateTime operationDate = DateTime.Now;

                DataTable dataTable = this.GetSameParentStep(database, rejectStepID);

                // 当驳回到第1步时，通过GetSameParentStep方法查询不出数据，此时将rejectStepID(要驳回到的步骤)添加到DataTable中
                if (dataTable.Rows.Count < 1)
                {
                    dataTable.Rows.Add(rejectStepID);
                }

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    dbCommand = this.GetDbCommand_FlowInstanceStep_Insert2(database, flowInstanceID, dataRow["StepID"].ToString()
                        , this.NextStepNumber, false);
                    dbCommandList.Add(dbCommand);

                    try
                    {
                        string email = FlowHelper.GetOperatorEmail(this.UrlParam_FlowID, dataRow["StepID"].ToString());
                        operatorEmails.Add(email);
                    }
                    catch (Exception ex1)
                    {
                        Logger.Write(null, 1, "email sender", "exception", ex1);
                    }
                }

                dbCommand = this.GetDbCommand_FlowInstanceStep_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_REJECT, operationDate, opinion, currentUser, true);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstanceStep_Update2(database, flowInstanceID, this.CurrentStepNumber, true);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstance_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_REJECT, operationDate, opinion, currentUser, false);
                dbCommandList.Add(dbCommand);

                dbConnection = database.CreateConnection();
                dbConnection.Open();
                dbTransaction = dbConnection.BeginTransaction();

                foreach (DbCommand dbCmd in dbCommandList)
                {
                    database.ExecuteNonQuery(dbCmd, dbTransaction);
                }

                dbTransaction.Commit();

                EmailSender.Send(operatorEmails);

                this.ShowStepInfoPage(this.UrlParam_FlowInstanceID);
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }
                helper.SetException(ex);
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
            }
        }

        protected void btnRevoke_Click(object sender, EventArgs e)
        {
            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();
            DbConnection dbConnection = null;
            DbTransaction dbTransaction = null;
            DbCommand dbCommand = null;
            List<DbCommand> dbCommandList = new List<DbCommand>();

            try
            {
                Guid flowInstanceID = new Guid(this.UrlParam_FlowInstanceID);                
                string currentUser = helper.CheckAndGetCurrentOperator();
                string flowCreateUser = this.GetFlowCreateUser(database, new Guid(this.UrlParam_FlowInstanceID));
                bool flowProcessed = this.FlowProcessed(database, new Guid(this.UrlParam_FlowInstanceID));
                if (!currentUser.ToUpper().Equals(flowCreateUser) || flowProcessed)
                {
                    return;
                }

                dbCommand = this.GetDbCommand_FlowInstanceStep_Delete(database, flowInstanceID);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstance_Delete(database, flowInstanceID);
                dbCommandList.Add(dbCommand);

                dbConnection = database.CreateConnection();
                dbConnection.Open();
                dbTransaction = dbConnection.BeginTransaction();

                foreach (DbCommand dbCmd in dbCommandList)
                {
                    database.ExecuteNonQuery(dbCmd, dbTransaction);
                }

                dbTransaction.Commit();
                helper.ShowMessagePage("操作成功");
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }
                helper.SetException(ex);
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
            }
        }

        protected void btnInvalid_Click(object sender, EventArgs e)
        {
            Helper helper = new Helper();
            Database database = DatabaseFactory.CreateDatabase();
            DbConnection dbConnection = null;
            DbTransaction dbTransaction = null;
            DbCommand dbCommand = null;
            List<DbCommand> dbCommandList = new List<DbCommand>();

            try
            {
                Guid flowInstanceID = new Guid(this.UrlParam_FlowInstanceID);
                string opinion = this.txtOpinion.Text.Trim();
                string currentUser = helper.CheckAndGetCurrentOperator();
                DateTime operationDate = DateTime.Now;

                dbCommand = this.GetDbCommand_FlowInstanceStep_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_INVALID, operationDate, opinion, currentUser, true);
                dbCommandList.Add(dbCommand);

                dbCommand = this.GetDbCommand_FlowInstance_Update(database, flowInstanceID, this.CurrentStepID
                    , this.CurrentStepNumber, OPERATION_INVALID, operationDate, opinion, currentUser, true);
                dbCommandList.Add(dbCommand);

                dbConnection = database.CreateConnection();
                dbConnection.Open();
                dbTransaction = dbConnection.BeginTransaction();

                foreach (DbCommand dbCmd in dbCommandList)
                {
                    database.ExecuteNonQuery(dbCmd, dbTransaction);
                }

                dbTransaction.Commit();
                helper.ShowMessagePage("操作成功");
            }
            catch (System.Threading.ThreadAbortException)
            {
                // do nothing. 
                // Helper.ShowMessagePage 方法中使用了 Response.Redirect，该方法会引发 ThreadAbortException 异常
                // 参考http://support.microsoft.com/kb/312629/
            }
            catch (Exception ex)
            {
                if (dbTransaction != null)
                {
                    dbTransaction.Rollback();
                }
                helper.SetException(ex);
            }
            finally
            {
                if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                {
                    dbConnection.Close();
                }
            }
        }        

        private void InitButton(Guid flowInstanceID)
        {
            btnCreate.Visible = false;
            btnAgree.Visible = false;
            btnReject.Visible = false;
            btnRevoke.Visible = false;
            btnInvalid.Visible = false;

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DataTable dtFlowInstance = this.GetFlowInstanceInfo(database, flowInstanceID);
                string currentUser = Helper.Instance.CheckAndGetCurrentOperator();

                if (string.IsNullOrWhiteSpace(currentUser))
                {
                    return;
                }

                // 是否存在业务数据
                bool hasBizData = this.HasBizData(database, new Guid(this.UrlParam_FlowInstanceID), this.TableName);

                if (!hasBizData)
                {
                    return;
                }

                // 是否存在流程数据
                bool hasFlowData = false;
                if (dtFlowInstance.Rows.Count > 0)
                {
                    hasFlowData = true;
                }

                // 如果存在流程数据，且流程已结束
                if (hasFlowData)
                {
                    DataRow dataRow = dtFlowInstance.Rows[0];
                    object objCompleted = dataRow["Completed"];
                   
                    if (objCompleted != null && objCompleted != DBNull.Value)
                    {
                        bool completed = false;
                        if (bool.TryParse(objCompleted.ToString(), out completed))
                        {
                            if (completed)
                            {
                                return;
                            }
                        }                        
                    }
                }

                // 执行到此的状态：有业务数据；没有流程数据，或有流程数据但流程未结束。                 

                // 获取当前用户在当前步骤下可执行的操作
                List<int> operationList = this.GetOperationsByCurrentUserAndCurrentStepID(database, this.CurrentStepID, currentUser);

                foreach (int operationID in operationList)
                {
                    switch (operationID)
                    {
                        // 发起
                        case OPERATION_CREATE:
                            if (!hasFlowData)
                            {
                                btnCreate.Visible = true;
                            }
                            if (btnCreate.Visible)
                            {
                                // 只有业务数据的创建人才能进行发起操作。
                                btnCreate.Visible = this.CurrentUserIsBizDataCreateUser(currentUser, database, new Guid(this.UrlParam_FlowInstanceID), this.TableName);
                            }
                            break;

                        // 同意
                        case OPERATION_AGREE:
                            if(hasFlowData)
                            {
                                btnAgree.Visible = true;
                            }
                            break;

                        // 驳回
                        case OPERATION_REJECT:
                            if (hasFlowData)
                            {
                                string parentStepID = this.GetParentStepID(database, this.CurrentStepID);
                                if (!string.IsNullOrWhiteSpace(parentStepID))
                                {
                                    btnReject.Visible = true;
                                }                                
                            }
                            break;

                        // 作废
                        case OPERATION_INVALID:
                            if (hasFlowData)
                            {
                                btnInvalid.Visible = true;
                            }
                            break;

                        default:
                            break;
                    }
                }
                
                // 判断当前登录人是否为流程提交人，且没有被审批，如果为否，则按钮不显示。
                string flowCreateUser = this.GetFlowCreateUser(database, new Guid(this.UrlParam_FlowInstanceID));
                bool flowProcessed = this.FlowProcessed(database, new Guid(this.UrlParam_FlowInstanceID));
                if (!currentUser.ToUpper().Equals(flowCreateUser) || flowProcessed)
                {
                    btnRevoke.Visible = false;
                }
                else
                {
                    btnRevoke.Visible = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private void ShowStepInfoPage(string flowInstanceID)
        {
            Response.Redirect(string.Format("FlowStepInfo.aspx?FlowInstanceID={0}", flowInstanceID));
        }

        #region DAL

        private bool FlowProcessed(Database database, Guid flowInstanceID)
        {
            string sql = "SELECT COUNT(ID) FROM dbo.FlowInstanceStep WHERE StepNumber = 2 AND Completed = 1 AND FlowInstanceID = @FlowInstanceID";
            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                object obj = database.ExecuteScalar(dbCommand);
                return Convert.ToBoolean(obj);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetFlowCreateUser(Database database, Guid flowInstanceID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInstanceInfo(database, flowInstanceID);

                if (dataTable.Rows.Count < 1)
                {
                    return string.Empty;
                }

                object obj = dataTable.Rows[0]["CreateUser"];
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetLastStepID(Database database, Guid flowInstanceID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInstanceInfo(database, flowInstanceID);

                if (dataTable.Rows.Count < 1)
                {
                    return string.Empty;
                }

                object obj = dataTable.Rows[0]["StepID"];
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;                
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        
        private string GetCurrentStepID(Database database, Guid flowInstanceID , string operatorInfo)
        {
            string sql = @"SELECT A.StepID FROM dbo.FlowInstanceStep A
                            INNER JOIN dbo.FlowInstance B ON A.FlowInstanceID = B.FlowInstanceID AND B.StepNumber + 1 = A.StepNumber
                            INNER JOIN dbo.FlowStepOperator C ON A.StepID = C.StepID AND C.OperatorInfo = @OperatorInfo
                            WHERE A.FlowInstanceID = @FlowInstanceID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, operatorInfo);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                object obj = database.ExecuteScalar(dbCommand);
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private int GetLastStepNumber(Database database, Guid flowInstanceID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInstanceInfo(database, flowInstanceID);

                if (dataTable.Rows.Count < 1)
                {
                    return 0;
                }

                object obj = dataTable.Rows[0]["StepNumber"];
                if (obj != null && obj != DBNull.Value)
                {
                    return int.Parse(obj.ToString());
                }
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetFormPath(Database database, string flowID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInfo(database, flowID);

                if (dataTable.Rows.Count < 1)
                {
                    return string.Empty;
                }

                object obj = dataTable.Rows[0]["FormPath"];
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;         
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetTableName(Database database, string flowID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInfo(database, flowID);

                if (dataTable.Rows.Count < 1)
                {
                    return string.Empty;
                }

                object obj = dataTable.Rows[0]["TableName"];
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetFlowName(Database database, string flowID)
        {
            try
            {
                DataTable dataTable = this.GetFlowInfo(database, flowID);

                if (dataTable.Rows.Count < 1)
                {
                    return string.Empty;
                }

                object obj = dataTable.Rows[0]["FlowName"];
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetCurrentStepName(Database database, string stepID)
        {
            string sql = @"SELECT StepName FROM dbo.FlowStep WHERE StepID = @StepID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                object obj = database.ExecuteScalar(dbCommand);

                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetFirstStep(Database database, string flowID)
        {
            string sql = @"SELECT A.StepID FROM dbo.FlowStepRelation A
                            INNER JOIN dbo.FlowStep B ON A.StepID = B.StepID AND B.FlowID = @FlowID
                            WHERE A.ParentID IS NULL";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowID", DbType.String, flowID);
                object obj = database.ExecuteScalar(dbCommand);
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable GetChildSteps(Database database, string stepID)
        {
            string sql = @"SELECT StepID FROM dbo.FlowStepRelation WHERE ParentID = @ParentID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@ParentID", DbType.String, stepID);
                DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable GetFlowInfo(Database database, string flowID)
        {
            string sql = @"SELECT * FROM dbo.FlowMain WHERE FlowID = @FlowID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowID", DbType.String, flowID);
                DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DataTable GetFlowInstanceInfo(Database database, Guid flowInstanceID)
        {
            string sql = @"SELECT * FROM dbo.FlowInstance WHERE FlowInstanceID = @FlowInstanceID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool HasBizData(Database database, Guid flowInstanceID, string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                return false;
            }

            string sql = @"SELECT FlowInstanceID FROM {0} WHERE FlowInstanceID = @FlowInstanceID";
            sql = string.Format(sql, tableName);

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                object obj = database.ExecuteScalar(dbCommand);
                if (obj != null && obj != DBNull.Value)
                {
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private bool CurrentUserIsBizDataCreateUser(string currentUser, Database database, Guid flowInstanceID, string tableName)
        {
            if (string.IsNullOrWhiteSpace(tableName))
            {
                return true;
            }

            // 有的表可能没有 CreateUser 列，如果出错先暂时返回 true

            string sql = @"SELECT CreateUser FROM {0} WHERE FlowInstanceID = @FlowInstanceID";
            sql = string.Format(sql, tableName);

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                object obj = database.ExecuteScalar(dbCommand);

                if (obj != null)
                {
                    if (currentUser.ToLower() != obj.ToString().ToLower())
                    {
                        return false;
                    }
                }

                return true;
            }
            catch
            {
                return true;
            }
        }

        private DataTable GetSameParentStep(Database database, string stepID)
        {
            string sql = @"SELECT DISTINCT StepID FROM dbo.FlowStepRelation 
                            WHERE ParentID IN (SELECT ParentID FROM dbo.FlowStepRelation WHERE StepID = @StepID)";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];
                return dataTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private string GetParentStepID(Database database, string stepID)
        {
            string sql = @"SELECT ParentID FROM dbo.FlowStepRelation WHERE StepID = @StepID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                object obj = database.ExecuteScalar(dbCommand);
                if (obj != null && obj != DBNull.Value)
                {
                    return obj.ToString();
                }
                return string.Empty;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        // 获取当前用户在当前步骤下可执行的操作
        private List<int> GetOperationsByCurrentUserAndCurrentStepID(Database database, string currentStepID, string currentUser)
        {
            string sql = @"SELECT A.OperationID FROM dbo.FlowStepOperation A
                              INNER JOIN dbo.FlowStepOperator B ON A.StepID = B.StepID AND B.OperatorInfo = @OperatorInfo
                              WHERE A.StepID = @CurrentStepID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@CurrentStepID", DbType.String, currentStepID);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, currentUser);
                DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];

                object operationID = null;
                List<int> list = new List<int>();

                foreach (DataRow dataRow in dataTable.Rows)
                {
                    operationID = dataRow["OperationID"];
                    if (operationID != null && operationID != DBNull.Value)
                    {
                        list.Add(int.Parse(operationID.ToString()));
                    }
                }

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #region 流程数据操作

        private DbCommand GetDbCommand_FlowInstance_Insert(Database database, Guid flowInstanceID, string flowID
            , string subject, string createUser, DateTime createDate, string stepID, int stepNumber, int operationID
            , DateTime operationDate, string opinion, string operatorInfo, bool completed)
        {
            string sql = @"INSERT INTO dbo.FlowInstance
		                            ([FlowInstanceID]
		                            ,[FlowID]
		                            ,[FlowName]
		                            ,[Subject]
		                            ,[CreateUser]
		                            ,[CreateDate]
                                    ,[StepID]
                                    ,[StepNumber]
                                    ,[StepName]
                                    ,[OperationID]
                                    ,[OperationName]
                                    ,[OperationDate]
                                    ,[Opinion]
                                    ,[OperatorInfo]
		                            ,[Completed])
	                            VALUES
		                            (@FlowInstanceID
		                            ,@FlowID
		                            ,(SELECT FlowName FROM dbo.FlowMain WHERE FlowID=@FlowID)
		                            ,@Subject
		                            ,@CreateUser
		                            ,@CreateDate
                                    ,@StepID
                                    ,@StepNumber
                                    ,(SELECT StepName FROM dbo.FlowStep WHERE StepID=@StepID)
                                    ,@OperationID
                                    ,(SELECT OperationName FROM dbo.FlowOperation WHERE OperationID = @OperationID)
                                    ,@OperationDate
                                    ,@Opinion
                                    ,@OperatorInfo
		                            ,@Completed)";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@FlowID", DbType.String, flowID);
                database.AddInParameter(dbCommand, "@Subject", DbType.String, subject);
                database.AddInParameter(dbCommand, "@CreateUser", DbType.String, createUser);
                database.AddInParameter(dbCommand, "@CreateDate", DbType.DateTime, createDate);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@OperationID", DbType.Int16, operationID);
                database.AddInParameter(dbCommand, "@OperationDate", DbType.DateTime, operationDate);
                database.AddInParameter(dbCommand, "@Opinion", DbType.String, opinion);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, operatorInfo);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstance_Update(Database database, Guid flowInstanceID, string stepID
            , int stepNumber, int operationID, DateTime operationDate, string opinion, string operatorInfo, bool completed)
        {
            string sql = @"UPDATE dbo.FlowInstance
                                   SET Completed = @Completed
                                      ,StepID = @StepID
                                      ,StepNumber = @StepNumber
                                      ,StepName = (SELECT StepName FROM dbo.FlowStep WHERE StepID=@StepID)
                                      ,OperationID = @OperationID
                                      ,OperationName = (SELECT OperationName FROM dbo.FlowOperation WHERE OperationID = @OperationID)
                                      ,OperationDate = @OperationDate
                                      ,Opinion = @Opinion
                                      ,OperatorInfo = @OperatorInfo
                                 WHERE FlowInstanceID = @FlowInstanceID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@OperationID", DbType.Int16, operationID);
                database.AddInParameter(dbCommand, "@OperationDate", DbType.DateTime, operationDate);
                database.AddInParameter(dbCommand, "@Opinion", DbType.String, opinion);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, operatorInfo);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstance_Delete(Database database, Guid flowInstanceID)
        {
            string sql = @"DELETE FROM dbo.FlowInstance WHERE FlowInstanceID = @FlowInstanceID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstanceStep_Insert(Database database, Guid flowInstanceID, string stepID
            , int stepNumber, int operationID, DateTime operationDate, string opinion, string operatorInfo, bool completed)
        {
            string sql = @"INSERT INTO [dbo].[FlowInstanceStep]
                                    ([FlowInstanceID]
                                    ,[StepID]
                                    ,[StepNumber]
                                    ,[StepName]
                                    ,[OperationID]
                                    ,[OperationName]
                                    ,[OperationDate]
                                    ,[Opinion]
                                    ,[OperatorInfo]
                                    ,[Completed])
                                VALUES
                                    (@FlowInstanceID
                                    ,@StepID
                                    ,@StepNumber
                                    ,(SELECT StepName FROM dbo.FlowStep WHERE StepID=@StepID)
                                    ,@OperationID
                                    ,(SELECT OperationName FROM dbo.FlowOperation WHERE OperationID = @OperationID)
                                    ,@OperationDate
                                    ,@Opinion
                                    ,@OperatorInfo
                                    ,@Completed)";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@OperationID", DbType.Int16, operationID);
                database.AddInParameter(dbCommand, "@OperationDate", DbType.DateTime, operationDate);
                database.AddInParameter(dbCommand, "@Opinion", DbType.String, opinion);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, operatorInfo);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstanceStep_Insert2(Database database, Guid flowInstanceID, string stepID
            , int stepNumber, bool completed)
        {
            string sql = @"INSERT INTO [dbo].[FlowInstanceStep]
                                    ([FlowInstanceID]
                                    ,[StepID]
                                    ,[StepNumber]
                                    ,[StepName]                                   
                                    ,[Completed])
                                VALUES
                                    (@FlowInstanceID
                                    ,@StepID
                                    ,@StepNumber
                                    ,(SELECT StepName FROM dbo.FlowStep WHERE StepID=@StepID)                                    
                                    ,@Completed)";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstanceStep_Update(Database database, Guid flowInstanceID, string stepID
            , int stepNumber, int operationID, DateTime operationDate, string opinion, string operatorInfo, bool completed)
        {
            string sql = @"UPDATE dbo.FlowInstanceStep
                                SET OperationID = @OperationID
                                ,OperationName = (SELECT OperationName FROM dbo.FlowOperation WHERE OperationID = @OperationID)
                                ,OperationDate = @OperationDate
                                ,Opinion = @Opinion
                                ,OperatorInfo = @OperatorInfo
                                ,Completed = @Completed
                                WHERE FlowInstanceID = @FlowInstanceID AND StepID = @StepID AND StepNumber = @StepNumber";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@StepID", DbType.String, stepID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@OperationID", DbType.Int16, operationID);
                database.AddInParameter(dbCommand, "@OperationDate", DbType.DateTime, operationDate);
                database.AddInParameter(dbCommand, "@Opinion", DbType.String, opinion);
                database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, operatorInfo);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstanceStep_Update2(Database database, Guid flowInstanceID, int stepNumber, bool completed)
        {
            string sql = @"UPDATE dbo.FlowInstanceStep SET Completed = 1                                                    
                           WHERE FlowInstanceID = @FlowInstanceID AND StepNumber = @StepNumber";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                database.AddInParameter(dbCommand, "@StepNumber", DbType.Int16, stepNumber);
                database.AddInParameter(dbCommand, "@Completed", DbType.Boolean, completed);

                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private DbCommand GetDbCommand_FlowInstanceStep_Delete(Database database, Guid flowInstanceID)
        {
            string sql = @"DELETE FROM dbo.FlowInstanceStep WHERE FlowInstanceID = @FlowInstanceID";

            try
            {
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.Guid, flowInstanceID);
                return dbCommand;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #endregion
    }
}